package com.jinbai.repo;

import com.jinbai.data_object.TidToCountDO;
import com.jinbai.util.DBUtil;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

public class RelationRepo {

    public RelationRepo() {
    }

    @SneakyThrows
    public static List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList) {
        List<TidToCountDO> ansList = new LinkedList<>();

        String sqlFormat = "select tid, count(*) from relations where tid in (%s) group by tid order by tid ";
        StringBuilder stringBuilder = new StringBuilder();
        for (Integer tid : tidList) {
            stringBuilder.append(tid).append(", ");
        }
        stringBuilder.delete(stringBuilder.length() - 2, stringBuilder.length());
        String sql = String.format(sqlFormat, stringBuilder.toString());
        try (Connection con = DBUtil.connection()) {
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        ansList.add(new TidToCountDO(
                                rs.getInt("tid"),
                                rs.getInt("count(*)")
                        ));
                    }
                }
            }
        }
        return ansList;
    }

    @SneakyThrows
    public List<Integer> selectTidListByAid(int aid) {
        List<Integer> ans = new LinkedList<>();
        try (Connection con = DBUtil.connection()) {
            String sql = "select tid from relations where aid = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, aid);
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        ans.add(rs.getInt("tid"));
                    }
                }
            }
        }
        return ans;
    }
}
